package Postgre;

import org.testng.annotations.*;

import java.sql.*;

import static org.testng.Assert.*;
import static org.testng.Assert.assertEquals;



/**
 * Created by IntelliJ IDEA.
 * User: Andrey
 * Date: 05.03.12
 * Time: 9:56
 * To change this template use File | Settings | File Templates.
 */
@Test()
public class testGetFieldsPostgre {

    private Connection con;

    @BeforeClass
    public void connect() throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");
        con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "andrey", "andrey");

        Statement st = con.createStatement();
        st.executeUpdate("create table test1 (id integer, value varchar(80))");
        st.executeUpdate("create table test2 (id integer, value integer)");
    }

    @AfterClass
    public void closeConnection() throws SQLException {
        Statement st = con.createStatement();
        st.execute("drop table test1");
        st.execute("drop table test2");

        con.close();
    }

    @Test
    public void testGetTables() throws SQLException {
        int ordPos;
        String colName;
        String dataType;
        Statement st = con.createStatement();

        ResultSet rs = st.executeQuery("SELECT  c.ordinal_position, c.column_name, c.data_type\n" +
                "   FROM information_schema.TABLES t JOIN information_schema.COLUMNS c ON t.table_name::text = c.table_name::text\n" +
                "  WHERE t.table_name = 'test1' AND \n" +
                "        t.table_schema::text = 'public'::text AND \n" +
                "        t.table_catalog::name = current_database() AND \n" +
                "        t.table_type::text = 'BASE TABLE'::text AND \n" +
                "        NOT \"substring\"(t.table_name::text, 1, 1) = '_'::text\n" +
                "  ORDER BY t.table_name, c.ordinal_position;");

        rs.next();
        ordPos = rs.getInt(1);
        colName = rs.getString(2);
        dataType = rs.getString(3);
        System.out.println("ordinal_position: " + ordPos + " column name " + colName + " data type " + dataType);
        assertEquals(ordPos, 1);
        assertEquals(colName, "id");
        assertEquals(dataType, "integer");

        rs.next();
        ordPos = rs.getInt(1);
        colName = rs.getString(2);
        dataType = rs.getString(3);
        System.out.println("ordinal_position: " + ordPos + " column name " + colName + " data type " + dataType);
        assertEquals(ordPos, 2);
        assertEquals(colName, "value");
        assertEquals(dataType, "character varying");

        rs = st.executeQuery("SELECT  c.ordinal_position, c.column_name, c.data_type\n" +
                "   FROM information_schema.TABLES t JOIN information_schema.COLUMNS c ON t.table_name::text = c.table_name::text\n" +
                "  WHERE t.table_name = 'test2' AND \n" +
                "        t.table_schema::text = 'public'::text AND \n" +
                "        t.table_catalog::name = current_database() AND \n" +
                "        t.table_type::text = 'BASE TABLE'::text AND \n" +
                "        NOT \"substring\"(t.table_name::text, 1, 1) = '_'::text\n" +
                "  ORDER BY t.table_name, c.ordinal_position;");

        rs.next();
        ordPos = rs.getInt(1);
        colName = rs.getString(2);
        dataType = rs.getString(3);
        System.out.println("ordinal position: " + ordPos + " column name " + colName + " data type " + dataType);
        assertEquals(ordPos, 1);
        assertEquals(colName, "id");
        assertEquals(dataType, "integer");

        rs.next();
        ordPos = rs.getInt(1);
        colName = rs.getString(2);
        dataType = rs.getString(3);
        System.out.println("ordinal position: " + ordPos + " column name " + colName + " data type " + dataType);
        assertEquals(ordPos, 2);
        assertEquals(colName, "value");
        assertEquals(dataType, "integer");
    }
}
